﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Web;
using System.Data;
using System.Configuration;
using KPIS.DBM;
using KPIS.GERP.GHRMS.MODEL;
using KPIS.GERP.GHRMS.IDAL;

namespace KPIS.GERP.GHRMS.DAL
{
    public class TrainingLecturerSkillsDAL : ITrainingLecturerSkillsDAL
    {
        DBManager dbManager = new DBManager(SystemInfo._DataProvider, ConfigurationSettings.AppSettings["ConnectionString"].ToString());
        ConvertNullable cn = new ConvertNullable();

        public IList<TrainingLecturerSkillsInfo> GetFull()
        {
            string strSQL;
            DataRowCollection drc = null;
            IList<TrainingLecturerSkillsInfo> infoList = new List<TrainingLecturerSkillsInfo>();
            try
            {
                dbManager.Open();
                dbManager.CreateParameters(1);

                dbManager.AddParameters(0, "@delete_status", SystemInfo._DeleteRecordStatus);

                strSQL = "SELECT tc.training_cap_seq, tc.trainer_seq"
                    + ", tc.training_subject_seq, ts.training_subject_descr"
                    + ", tr.name_prefix_seq, tr.first_name_local, tr.last_name_local, mp.prefix_short"
                    + " FROM hrm_trainers_capabilities tc"
                    + " Left Join hrm_training_subjects ts ON tc.training_subject_seq = ts.training_subject_seq"
                    + " Left Join hrm_trainers tr ON tc.trainer_seq = tr.trainer_seq"
                    + " Left Join mas_name_prefixes mp ON tr.name_prefix_seq = mp.name_prefix_seq" 
                    + " WHERE tc.record_status not like @delete_status"
                    + " AND tr.record_status not like @delete_status";

                drc = dbManager.ExecuteDataSet(CommandType.Text, strSQL).Tables[0].Rows;
                if (drc.Count > 0)
                {
                    for (int i = 0; i < drc.Count; i++)
                    {
                        TrainingLecturerSkillsInfo info = new TrainingLecturerSkillsInfo();
                        info.training_cap_seq = Convert.ToInt32(drc[i]["training_cap_seq"]);
                        info.trainer_seq = Convert.ToInt32(drc[i]["trainer_seq"]);
                        info.trainer = drc[i]["prefix_short"].ToString() + " " + drc[i]["first_name_local"].ToString() + " " + drc[i]["last_name_local"].ToString();
                        info.training_subject_seq = Convert.ToInt32(drc[i]["training_subject_seq"]);
                        info.training_subject = drc[i]["training_subject_descr"].ToString();

                        infoList.Add(info);

                    }
                }
            }
            catch (Exception ex)
            {
                throw (ex);
            }
            finally
            {
                dbManager.Dispose();
            }

            return infoList;
        }
        public IList<TrainingLecturerSkillsInfo> GetBySubject(Nullable<int>train_seq)
        {
            string strSQL;
            DataRowCollection drc = null;
            IList<TrainingLecturerSkillsInfo> infoList = new List<TrainingLecturerSkillsInfo>();
            try
            {
                dbManager.Open();
                dbManager.CreateParameters(2);

                dbManager.AddParameters(0, "@delete_status", SystemInfo._DeleteRecordStatus);
                dbManager.AddParameters(1, "@train_seq", train_seq);

                strSQL = "SELECT hrm_trainers.name_prefix_seq"
                        + ", hrm_trainers.first_name_local"
                        + ", hrm_trainers.last_name_local"
                        + ", hrm_trainers_capabilities.training_subject_seq"
                        + ", hrm_trainers_capabilities.training_cap_seq"
                        + ", hrm_training_subjects.training_subject_descr"
                        + ", hrm_training_subjects_dtls.topic_descr"
                        + ", hrm_trainers.trainer_seq"
                        + ", hrm_training_curriculums.training_cur_descr"
                        + ", hrm_training_courses.train_seq"
                        + ", hrm_training_courses.training_course_descr"
                        + ", hrm_training_curriculums.training_cur_seq"
                        + " FROM hrm_trainers"
                        + " Left Join hrm_trainers_capabilities ON hrm_trainers.trainer_seq = hrm_trainers_capabilities.trainer_seq"
                        + " Left Join hrm_training_subjects ON hrm_trainers_capabilities.training_subject_seq = hrm_training_subjects.training_subject_seq"
                        + " Left Join hrm_training_subjects_dtls ON hrm_training_subjects.training_subject_seq = hrm_training_subjects_dtls.training_subject_seq"
                        + " Left Join hrm_training_cur_subjects ON hrm_training_subjects.training_subject_seq = hrm_training_cur_subjects.training_subject_seq"
                        + " Left Join hrm_training_curriculums ON hrm_training_cur_subjects.training_cur_seq = hrm_training_curriculums.training_cur_seq"
                        + " Left Join hrm_training_courses ON hrm_training_courses.training_cur_seq = hrm_training_curriculums.training_cur_seq"
                        + " WHERE hrm_trainers_capabilities.record_status not like @delete_status"
                        + " AND hrm_trainers.record_status not like @delete_status"
                        + (train_seq > 0 ? " AND hrm_training_courses.train_seq = @train_seq " : "")
                        //+ " group by  hrm_trainers_capabilities.training_cap_seq";
                        +" group by hrm_trainers.name_prefix_seq, hrm_trainers.first_name_local, "
                        +" hrm_trainers.last_name_local, hrm_trainers_capabilities.training_subject_seq, "
                        +" hrm_trainers_capabilities.training_cap_seq, hrm_training_subjects.training_subject_descr, "
                        +" hrm_training_subjects_dtls.topic_descr, hrm_trainers.trainer_seq, "
                        +" hrm_training_curriculums.training_cur_descr, hrm_training_courses.train_seq, "
                        +" hrm_training_courses.training_course_descr, hrm_training_curriculums.training_cur_seq ";

                drc = dbManager.ExecuteDataSet(CommandType.Text, strSQL).Tables[0].Rows;
                if (drc.Count > 0)
                {
                    for (int i = 0; i < drc.Count; i++)
                    {
                        TrainingLecturerSkillsInfo info = new TrainingLecturerSkillsInfo();
                        info.training_cap_seq = Convert.ToInt32(drc[i]["training_cap_seq"]);
                        info.trainer_seq = Convert.ToInt32(drc[i]["trainer_seq"]);
                        info.trainer = drc[i]["first_name_local"].ToString() + " " + drc[i]["last_name_local"].ToString();
                        info.training_subject_seq = Convert.ToInt32(drc[i]["training_subject_seq"]);
                        info.training_subject = drc[i]["training_subject_descr"].ToString();

                        infoList.Add(info);

                    }
                }
            }
            catch (Exception ex)
            {
                throw (ex);
            }
            finally
            {
                dbManager.Dispose();
            }

            return infoList;
        }

        public void Insert(TrainingLecturerSkillsInfo info)
        {
            string strSQL;
            try
            {
                dbManager.Open();
                dbManager.CreateParameters(5);

                dbManager.AddParameters(0, "@trainer_seq", info.trainer_seq);
                dbManager.AddParameters(1, "@training_subject_seq", info.training_subject_seq);
                dbManager.AddParameters(2, "@record_status", SystemInfo._ActiveRecordStatus);
                dbManager.AddParameters(3, "@created_by", info.created_by);
                dbManager.AddParameters(4, "@created_when", info.created_when);

                strSQL = "INSERT INTO hrm_trainers_capabilities"
                    + "(trainer_seq, training_subject_seq, record_status"
                    + ", created_by, created_when)"
                    + " VALUES (@trainer_seq"
                    + ", @training_subject_seq"
                    + ", @record_status"
                    + ", @created_by"
                    + ", @created_when)";

                dbManager.ExecuteNonQuery(CommandType.Text, strSQL);
            }
            catch
            {
                throw;
            }
            finally
            {
                dbManager.Dispose();
            }
        }
        public void Delete(TrainingLecturerSkillsInfo info)
        {
            string strSQL;
            try
            {
                dbManager.Open();
                dbManager.CreateParameters(4);

                dbManager.AddParameters(0, "@training_cap_seq", info.training_cap_seq);
                dbManager.AddParameters(1, "@record_status", SystemInfo._DeleteRecordStatus);
                dbManager.AddParameters(2, "@updated_by", info.updated_by);
                dbManager.AddParameters(3, "@updated_when", info.updated_when);

                strSQL = "UPDATE hrm_trainers_capabilities"
                    + " SET record_status = @record_status"
                    + ", updated_by = @updated_by"
                    + ", updated_when = @updated_when"
                    + " WHERE training_cap_seq = @training_cap_seq";

                dbManager.ExecuteNonQuery(CommandType.Text, strSQL);
            }
            catch
            {
                throw;
            }
            finally
            {
                dbManager.Dispose();
            }
        }

        public void Update(string strTranSeq, string TranSubSeq, string TrainerCapSeq)
        {
            string strSQL;
            try
            {
                dbManager.Open();
                dbManager.CreateParameters(4);

                dbManager.AddParameters(0, "@trainer_seq", strTranSeq);
                dbManager.AddParameters(1, "@training_subject_seq", TranSubSeq);
                dbManager.AddParameters(2, "@training_cap_seq", TrainerCapSeq);
                dbManager.AddParameters(3, "@delete_status", SystemInfo._DeleteRecordStatus);

                strSQL = " Update hrm_trainers_capabilities SET "
                        + " trainer_seq = @trainer_seq "
                        + " ,training_subject_seq = @training_subject_seq"
                        + " WHERE training_cap_seq = @training_cap_seq"
                        + " AND record_status not like @delete_status";

                dbManager.ExecuteNonQuery(CommandType.Text, strSQL);
            }
            catch (Exception)
            {
                throw;
            }
            finally
            {
                dbManager.Dispose();
            }
        }

        public DataSet CheckDuplicate(int trainer_seq, int training_subject_seq)
        {
            DataSet ds = new DataSet();
            dbManager.Open();
            dbManager.CreateParameters(3);
            dbManager.AddParameters(0, "@trainer_seq", trainer_seq);
            dbManager.AddParameters(1, "@training_subject_seq", training_subject_seq);
            dbManager.AddParameters(2, "@record_status", SystemInfo._ActiveRecordStatus);

            string sql = "SELECT tc.training_cap_seq"
                        + " FROM hrm_trainers_capabilities tc"
                        + " WHERE tc.trainer_seq = @trainer_seq"
                        + " AND tc.training_subject_seq = @training_subject_seq"
                        + " AND tc.record_status = @record_status";

            ds = dbManager.ExecuteDataSet(CommandType.Text, sql);
            dbManager.Dispose();
            return ds;
        }
    }
}
